package com.ysd.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.sun.corba.se.spi.orbutil.fsm.State;
import com.ysd.entity.ChanPinXiangQing;
import com.ysd.util.DBManager;

public class ChanPinXiangQingDaoimpl {
	/**
	 * 通过chanPingId查询产品详情的方法
	 * @return
	 */
	public ArrayList<ChanPinXiangQing> getChanPinXiangQing(int id){
		Connection conn=DBManager.getConnection();
		ArrayList<ChanPinXiangQing> list=new ArrayList<ChanPinXiangQing>();
		Statement st=null;
		ResultSet rs=null;
		try {
			 st=conn.createStatement();
			String sql="SELECT * FROM chanpinxiangqing where chanPingId="+id;
			 rs=st.executeQuery(sql);
			while(rs.next()) {
				ChanPinXiangQing cpxq=new ChanPinXiangQing(rs.getInt("chanPingId"), rs.getString("chanPinName"), rs.getString("chanPinDiZhi"), rs.getInt("chanPinShouJia"), rs.getInt("chanPinShiChangJia"), rs.getString("chanPinPaiZi"), rs.getString("shiFouGuoqi"), rs.getString("shiFouXianLiang"), rs.getString("baoZhiQi"), rs.getString("jiaGongFang"), rs.getString("kouWei"), rs.getString("shenChangShang"), rs.getString("zhongLiang"), rs.getInt("fenleiId"),rs.getString("imgs"));
				list.add(cpxq);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
		
	}
	
	
	
	
	
	/**
	 * 随机显示6条数据
	 * @return
	 */
	public ArrayList<ChanPinXiangQing> getJingPingAnLi(){
		Connection conn=DBManager.getConnection();
		ArrayList<ChanPinXiangQing> list=new ArrayList<ChanPinXiangQing>();
		Statement st=null;
		ResultSet rs=null;
		try {
			 st=conn.createStatement();
			String sql="SELECT * FROM chanpinxiangqing ORDER BY RAND() limit 6";
			 rs=st.executeQuery(sql);
			while(rs.next()) {
				
				ChanPinXiangQing cpxq=new ChanPinXiangQing(rs.getInt("chanPingId"), rs.getString("chanPinName"), rs.getString("chanPinDiZhi"), rs.getInt("chanPinShouJia"), rs.getInt("chanPinShiChangJia"), rs.getString("chanPinPaiZi"), rs.getString("shiFouGuoqi"), rs.getString("shiFouXianLiang"), rs.getString("baoZhiQi"), rs.getString("jiaGongFang"), rs.getString("kouWei"), rs.getString("shenChangShang"), rs.getString("zhongLiang"), rs.getInt("fenleiId"),rs.getString("imgs"));
				list.add(cpxq);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
		
	}
	/**
	 * 随机显示一条数据
	 * @return
	 */
	public ArrayList<ChanPinXiangQing> getJingXuanXinWenTuPian(){
		Connection conn=DBManager.getConnection();
		ArrayList<ChanPinXiangQing> list=new ArrayList<ChanPinXiangQing>();
		Statement st=null;
		ResultSet rs=null;
		try {
			 st=conn.createStatement();
			String sql="SELECT * FROM chanpinxiangqing ORDER BY RAND() limit 1";
			 rs=st.executeQuery(sql);
			while(rs.next()) {
				ChanPinXiangQing cpxq=new ChanPinXiangQing(rs.getInt("chanPingId"), rs.getString("chanPinName"), rs.getString("chanPinDiZhi"), rs.getInt("chanPinShouJia"), rs.getInt("chanPinShiChangJia"), rs.getString("chanPinPaiZi"), rs.getString("shiFouGuoqi"), rs.getString("shiFouXianLiang"), rs.getString("baoZhiQi"), rs.getString("jiaGongFang"), rs.getString("kouWei"), rs.getString("shenChangShang"), rs.getString("zhongLiang"), rs.getInt("fenleiId"),rs.getString("imgs"));
				list.add(cpxq);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
		
	}
	
	
	
	/**
	 * 根据id查询产品详情
	 * @param id
	 * @return
	 */
	public ArrayList<ChanPinXiangQing> getChanPinXiangQingId(int id){
		Connection conn=DBManager.getConnection();
		ArrayList<ChanPinXiangQing> list=new ArrayList<ChanPinXiangQing>();
		Statement st=null;
		ResultSet rs=null;
		try {
			 st=conn.createStatement();
			String sql="SELECT * FROM chanpinxiangqing where fenleiId="+id;
			 rs=st.executeQuery(sql);
			while(rs.next()) {
				ChanPinXiangQing cpxq=new ChanPinXiangQing(rs.getInt("chanPingId"), rs.getString("chanPinName"), rs.getString("chanPinDiZhi"), rs.getInt("chanPinShouJia"), rs.getInt("chanPinShiChangJia"), rs.getString("chanPinPaiZi"), rs.getString("shiFouGuoqi"), rs.getString("shiFouXianLiang"), rs.getString("baoZhiQi"), rs.getString("jiaGongFang"), rs.getString("kouWei"), rs.getString("shenChangShang"), rs.getString("zhongLiang"), rs.getInt("fenleiId"),rs.getString("imgs"));
				list.add(cpxq);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
		
	}
	
	
	/**
	 * 获得所有产品详情
	 * @return
	 */
	public ArrayList<ChanPinXiangQing> getAllChanPinXiangQing(){
		Connection conn=DBManager.getConnection();
		ArrayList<ChanPinXiangQing> list=new ArrayList<ChanPinXiangQing>();
		Statement st=null;
		ResultSet rs=null;
		try {
			 st=conn.createStatement();
			String sql="select  * from chanpinxiangqing c,fenleibiao f  where c.fenleiId=f.chanPinId";
			 rs=st.executeQuery(sql);
			while(rs.next()) {
				ChanPinXiangQing cpxq=new ChanPinXiangQing(rs.getInt("chanPingId"), rs.getString("chanPinName"), rs.getString("chanPinDiZhi"), rs.getInt("chanPinShouJia"), rs.getInt("chanPinShiChangJia"), rs.getString("chanPinPaiZi"), rs.getString("shiFouGuoqi"), rs.getString("shiFouXianLiang"), rs.getString("baoZhiQi"), rs.getString("jiaGongFang"), rs.getString("kouWei"), rs.getString("shenChangShang"), rs.getString("zhongLiang"), rs.getInt("fenleiId"),rs.getString("imgs"),rs.getInt("chanPinId"),rs.getString("leiMing"));
				list.add(cpxq);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
		
	}
	
	/**
	 * 根据id删除产品详情
	 * @param id
	 * @return
	 */
	public int delChanPingXiangQingId(int id) {
		return DBManager.executeUpdate("delete from chanpinxiangqing WHERE chanpingId="+id);
	}
	
	/**
	 * 多条件查询产品详情
	 * @param c
	 * @return
	 */
	public ArrayList<ChanPinXiangQing> getChanPingXiangQingWhere(ChanPinXiangQing c,int page,int size){
		Connection conn=DBManager.getConnection();
		Statement st=null;
		ResultSet rs=null;
		ArrayList<ChanPinXiangQing> list =new ArrayList<ChanPinXiangQing>();
		try {
		 st=conn.createStatement();
		String sql="select c.*,f.leiMing from chanpinxiangqing c,fenleibiao f where 1=1 ";
		if(c.getChanPinName()!=null && !c.getChanPinName().equals("")) {
			sql+=" and chanPinName LIKE '%"+c.getChanPinName()+"%'";
		}
		
		if(c.getChanPinDiZhi()!=null && !c.getChanPinDiZhi().equals("")) {
			sql+=" and chanPinDiZhi LIKE '%"+c.getChanPinDiZhi()+"%'";
		}
		
		if(c.getQiShiJiaGe()>0) {
			sql+=" and chanPinShouJia>"+c.getQiShiJiaGe();
		}
		if(c.getJieShuJiaGe()>0) {
			sql+=" and chanPinShouJia<"+c.getJieShuJiaGe();
		}
		
		if(c.getChanPinPaiZi()!=null && !c.getChanPinPaiZi().equals("")) {
			sql+=" and chanPinPaiZi LIKE '%"+c.getChanPinPaiZi()+"%'";
		}
		
		if(c.getShiFouGuoqi()!=null && !c.getShiFouGuoqi().equals("")) {
			sql+=" and shiFouGuoqi LIKE '%"+c.getShiFouGuoqi()+"%'";
		}
		
		if(c.getShiFouXianLiang()!=null && !c.getShiFouXianLiang().equals("")) {
			sql+=" and shiFouXianLiang LIKE '%"+c.getShiFouXianLiang()+"%'";
		}
		
		if(c.getBaoZhiQi()!=null && !c.getBaoZhiQi().equals("")) {
			sql+=" and baoZhiQi LIKE '%"+c.getBaoZhiQi()+"%'";
		}
		
		
		if(c.getJiaGongFang()!=null && !c.getJiaGongFang().equals("")) {
			sql+=" and jiaGongFang LIKE '%"+c.getJiaGongFang()+"%'";
		}
		
		
		if(c.getKouWei()!=null && !c.getKouWei().equals("")) {
			sql+=" and kouWei LIKE '%"+c.getKouWei()+"%'";
		}
		
		
		if(c.getShenChangShang()!=null && !c.getShenChangShang().equals("")) {
			sql+=" and shenChangShang LIKE '%"+c.getShenChangShang()+"%'";
		}
		
		if(c.getFenleiId()>0) {
			sql+=" and fenleiId="+c.getFenleiId();
		}
		sql+=" and c.fenleiId=f.chanPinId ORDER BY chanPingId limit "+(page-1)*size+", "+size;
		System.out.println(sql);
		 rs=st.executeQuery(sql);
		while(rs.next()) {
			int chanPingId=rs.getInt("chanPingId");
			String imgs=rs.getString("imgs");
			
			String chanPinName=rs.getString("chanPinName");
			String chanPinDiZhi=rs.getString("chanPinDiZhi");
			int chanPinShouJia=rs.getInt("chanPinShouJia");
			int chanPinShiChangJia=rs.getInt("chanPinShiChangJia");

			String chanPinPaiZi=rs.getString("chanPinPaiZi");
			String shiFouGuoqi=rs.getString("shiFouGuoqi");
			String shiFouXianLiang=rs.getString("shiFouXianLiang");
			String baoZhiQi=rs.getString("baoZhiQi");
			String jiaGongFang=rs.getString("jiaGongFang");
			String kouWei=rs.getString("kouWei");
			String shenChangShang=rs.getString("shenChangShang");
			String zhongLiang=rs.getString("zhongLiang");
			
			int fenleiId=rs.getInt("fenleiId");
			String leiMing=rs.getString("leiMing");
			
		
			ChanPinXiangQing cpxq=new ChanPinXiangQing(chanPingId, chanPinName, chanPinDiZhi, chanPinShouJia, chanPinShiChangJia, chanPinPaiZi, shiFouGuoqi, shiFouXianLiang, baoZhiQi, jiaGongFang, kouWei, shenChangShang, zhongLiang, fenleiId, imgs, leiMing, 0, 0);
			list.add(cpxq);
		}
		return list;
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	
	
	/**
	 * 添加产品
	 * @param c
	 * @return
	 */
	public int addChanPingXiangQing(ChanPinXiangQing c) {
		return DBManager.executeUpdate("INSERT into chanpinxiangqing VALUES(null,'"+c.getImgs()+"','"+c.getChanPinName()+"','"+c.getChanPinDiZhi()+"','"+c.getChanPinShouJia()+"','"+c.getChanPinShiChangJia()+"','"+c.getChanPinPaiZi()+"','"+c.getShiFouGuoqi()+"','"+c.getShiFouXianLiang()+"','"+c.getBaoZhiQi()+"','"+c.getJiaGongFang()+"','"+c.getKouWei()+"','"+c.getShenChangShang()+"','"+c.getZhongLiang()+"',"+c.getFenleiId()+")");
	}
	
	
	
	/**
	 * 通过name值查询
	 * @param name
	 * @return
	 */
	public ArrayList<ChanPinXiangQing> selChanPingXiangQingByName(String name){
		ArrayList<ChanPinXiangQing> list=new ArrayList<ChanPinXiangQing>();
		Connection conn=DBManager.getConnection();
		Statement st=null;
		ResultSet rs=null;
		try {
			 st=conn.createStatement();
			 rs=st.executeQuery("select * from chanpinxiangqing where chanPinName='"+name+"'");
			while(rs.next()) {
				ChanPinXiangQing cpxq=new ChanPinXiangQing(rs.getInt("chanPingId"), rs.getString("chanPinName"), rs.getString("chanPinDiZhi"), rs.getInt("chanPinShouJia"), rs.getInt("chanPinShiChangJia"), rs.getString("chanPinPaiZi"), rs.getString("shiFouGuoqi"), rs.getString("shiFouXianLiang"), rs.getString("baoZhiQi"), rs.getString("jiaGongFang"), rs.getString("kouWei"), rs.getString("shenChangShang"), rs.getString("zhongLiang"), rs.getInt("fenleiId"),rs.getString("imgs"));
				list.add(cpxq);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	
	
	/**
	 * 通过产品id查询
	 * @param id
	 * @return
	 */
	public ChanPinXiangQing getChanPinXiangQingbyId(int id){
		Connection conn=DBManager.getConnection();
		ChanPinXiangQing cpxq=null;
		Statement st=null;
		ResultSet rs=null;
		try {
			 st=conn.createStatement();
			String sql="SELECT * FROM chanpinxiangqing where chanPingId="+id;
			 rs=st.executeQuery(sql);
			while(rs.next()) {
				 cpxq=new ChanPinXiangQing(rs.getInt("chanPingId"), rs.getString("chanPinName"), rs.getString("chanPinDiZhi"), rs.getInt("chanPinShouJia"), rs.getInt("chanPinShiChangJia"), rs.getString("chanPinPaiZi"), rs.getString("shiFouGuoqi"), rs.getString("shiFouXianLiang"), rs.getString("baoZhiQi"), rs.getString("jiaGongFang"), rs.getString("kouWei"), rs.getString("shenChangShang"), rs.getString("zhongLiang"), rs.getInt("fenleiId"),rs.getString("imgs"));
			}	
			return cpxq;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
		
	}
	public int editChanPinXiangQing(ChanPinXiangQing c) {
		String sql="UPDATE chanpinxiangqing set imgs='"+c.getImgs()+"',chanPinName='"+c.getChanPinName()+"',chanPinDiZhi='"+c.getChanPinDiZhi()+"',chanPinShouJia='"+c.getChanPinShouJia()+"',chanPinShiChangJia='"+c.getChanPinShiChangJia()+"',chanPinPaiZi='"+c.getChanPinPaiZi()+"',shiFouGuoqi='"+c.getShiFouGuoqi()+"',shiFouXianLiang='"+c.getShiFouXianLiang()+"',baoZhiQi='"+c.getBaoZhiQi()+"',jiaGongFang='"+c.getJiaGongFang()+"',kouWei='"+c.getKouWei()+"',shenChangShang='"+c.getShenChangShang()+"',zhongLiang='"+c.getZhongLiang()+"',fenleiId='"+c.getFenleiId()+"' WHERE chanPingId="+c.getChanPingId();
		
		return	DBManager.executeUpdate(sql);
	}
	
	
	
	public ArrayList<ChanPinXiangQing> selChanPingXiangQingId(){
		ArrayList<ChanPinXiangQing> list=new ArrayList<ChanPinXiangQing>();
		Connection conn=DBManager.getConnection();
		Statement st=null;
		ResultSet rs=null;
		try {
			 st=conn.createStatement();
			 rs=st.executeQuery("select chanpingid from chanpinxiangqing");
			while(rs.next()) {
				ChanPinXiangQing cpxq=new ChanPinXiangQing(rs.getInt("chanPingId"));
				list.add(cpxq);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	
	
	
	public ArrayList<ChanPinXiangQing> getChanPingXiangQingWhereByPage(ChanPinXiangQing c){
		Connection conn=DBManager.getConnection();
		Statement st=null;
		ResultSet rs=null;
		ArrayList<ChanPinXiangQing> list =new ArrayList<ChanPinXiangQing>();
		try {
		 st=conn.createStatement();
		String sql="select c.*,f.leiMing from chanpinxiangqing c,fenleibiao f where 1=1 ";
		if(c.getChanPinName()!=null && !c.getChanPinName().equals("")) {
			sql+=" and chanPinName LIKE '%"+c.getChanPinName()+"%'";
		}
		
		if(c.getChanPinDiZhi()!=null && !c.getChanPinDiZhi().equals("")) {
			sql+=" and chanPinDiZhi LIKE '%"+c.getChanPinDiZhi()+"%'";
		}
		
		if(c.getQiShiJiaGe()>0) {
			sql+=" and chanPinShouJia>"+c.getQiShiJiaGe();
		}
		if(c.getJieShuJiaGe()>0) {
			sql+=" and chanPinShouJia<"+c.getJieShuJiaGe();
		}
		
		if(c.getChanPinPaiZi()!=null && !c.getChanPinPaiZi().equals("")) {
			sql+=" and chanPinPaiZi LIKE '%"+c.getChanPinPaiZi()+"%'";
		}
		
		if(c.getShiFouGuoqi()!=null && !c.getShiFouGuoqi().equals("")) {
			sql+=" and shiFouGuoqi LIKE '%"+c.getShiFouGuoqi()+"%'";
		}
		
		if(c.getShiFouXianLiang()!=null && !c.getShiFouXianLiang().equals("")) {
			sql+=" and shiFouXianLiang LIKE '%"+c.getShiFouXianLiang()+"%'";
		}
		
		if(c.getBaoZhiQi()!=null && !c.getBaoZhiQi().equals("")) {
			sql+=" and baoZhiQi LIKE '%"+c.getBaoZhiQi()+"%'";
		}
		
		
		if(c.getJiaGongFang()!=null && !c.getJiaGongFang().equals("")) {
			sql+=" and jiaGongFang LIKE '%"+c.getJiaGongFang()+"%'";
		}
		
		
		if(c.getKouWei()!=null && !c.getKouWei().equals("")) {
			sql+=" and kouWei LIKE '%"+c.getKouWei()+"%'";
		}
		
		
		if(c.getShenChangShang()!=null && !c.getShenChangShang().equals("")) {
			sql+=" and shenChangShang LIKE '%"+c.getShenChangShang()+"%'";
		}
		
		if(c.getFenleiId()>0) {
			sql+=" and fenleiId="+c.getFenleiId();
		}
		sql+=" and c.fenleiId=f.chanPinId";
		System.out.println(sql);
		 rs=st.executeQuery(sql);
		while(rs.next()) {
			int chanPingId=rs.getInt("chanPingId");
			String imgs=rs.getString("imgs");
			
			String chanPinName=rs.getString("chanPinName");
			String chanPinDiZhi=rs.getString("chanPinDiZhi");
			int chanPinShouJia=rs.getInt("chanPinShouJia");
			int chanPinShiChangJia=rs.getInt("chanPinShiChangJia");

			String chanPinPaiZi=rs.getString("chanPinPaiZi");
			String shiFouGuoqi=rs.getString("shiFouGuoqi");
			String shiFouXianLiang=rs.getString("shiFouXianLiang");
			String baoZhiQi=rs.getString("baoZhiQi");
			String jiaGongFang=rs.getString("jiaGongFang");
			String kouWei=rs.getString("kouWei");
			String shenChangShang=rs.getString("shenChangShang");
			String zhongLiang=rs.getString("zhongLiang");
			
			int fenleiId=rs.getInt("fenleiId");
			String leiMing=rs.getString("leiMing");
			
		
			ChanPinXiangQing cpxq=new ChanPinXiangQing(chanPingId, chanPinName, chanPinDiZhi, chanPinShouJia, chanPinShiChangJia, chanPinPaiZi, shiFouGuoqi, shiFouXianLiang, baoZhiQi, jiaGongFang, kouWei, shenChangShang, zhongLiang, fenleiId, imgs, leiMing, 0, 0);
			list.add(cpxq);
		}
		return list;
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	
}
